﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace Overview
{
    class DataHelper
    {
        private MySqlConnection connection;
        public DataHelper()
        {


            String connectionInfo = "server=athena01.fhict.local;" +
                             "database=dbi269964;" + // your database name
                            "user id=dbi269964;" + // your user id
                              "password=ztauJSorfR;" + // your password
                             "connect timeout=30;";
            connection = new MySqlConnection(connectionInfo);

        }
        public int totalVisitors()// returns the total number of visitors that have come to the event.
        {
            String sql = "SELECT COUNT(*) FROM event_status";
            MySqlCommand command = new MySqlCommand(sql, connection);
            int number = 0;
            try
            {
                connection.Open();
                number = Convert.ToInt32(command.ExecuteScalar());
                return number;
            }
            catch
            {
                return -1;

            }
            finally
            {
                connection.Close();
            }
        }
        public int totalExits()// returns the number of times someone has exited the whole event.
        {
            String sql = "SELECT COUNT(*) FROM event_status Where HasLeftEvent = 'y'";
            MySqlCommand command = new MySqlCommand(sql, connection);
            int number = 0;
            try
            {
                connection.Open();
                number = Convert.ToInt32(command.ExecuteScalar());
                return number;
            }
            catch
            {
                return -1;

            }
            finally
            {
                connection.Close();
            }
        }
        public int campExits()// returns the amount of people that have exited the camp site
        {
            String sql = "SELECT COUNT(*) FROM camping_status Where HasLeftCamping = 'y'";
            MySqlCommand command = new MySqlCommand(sql, connection);
            int number = 0;
            try
            {
                connection.Open();
                number = Convert.ToInt32(command.ExecuteScalar());
                return number;
            }
            catch
            {
                return -1;

            }
            finally
            {
                connection.Close();
            }
        }

        public int onTheTerrain()// the amount of people on the event site at this particular time
        {
            String sql = "SELECT COUNT(*) FROM camping_status Where HasEnteredCamping = 'y'";
            MySqlCommand command = new MySqlCommand(sql, connection);
            int number = 0;
            try
            {
                connection.Open();
                number = Convert.ToInt32(command.ExecuteScalar());
                return number;
            }
            catch
            {
                return -1;

            }
            finally
            {
                connection.Close();
            }
        }
        public int CampingSpotsTaken()// the Amount of camping spots taken
        {
            String sql = "SELECT COUNT(*) FROM camping_spot Where Status = 'booked'";
            MySqlCommand command = new MySqlCommand(sql, connection);
            int number = 0;
            try
            {
                connection.Open();
                number = Convert.ToInt32(command.ExecuteScalar());
                return number;
            }
            catch
            {
                return -1;

            }
            finally
            {
                connection.Close();
            }
        }

        public double totalMoneySpent()// returns the total money spent by quests at the event.
        {
            String sql = "SELECT SUM(MoneySpent) FROM event_account";
            MySqlCommand command = new MySqlCommand(sql, connection);
            double number = 0;
            try
            {
                connection.Open();
                number = Convert.ToDouble(command.ExecuteScalar());
                return number;
            }
            catch
            {
                return -1;

            }
            finally
            {
                connection.Close();
            }
        }
        public double totalMoneyAccount()// total Amount of money on Accounts
        {
            String sql = "SELECT SUM(MoneyDeposited) FROM event_account";
            MySqlCommand command = new MySqlCommand(sql, connection);
            double number = 0;
            try
            {
                connection.Open();
                number = Convert.ToDouble(command.ExecuteScalar());
                return number;
            }
            catch
            {
                return -1;

            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        ///Return Account information from a certain visitor.
        /// </summary>
        /// <param name="Name">the name of a visitor</param>
        /// <returns></returns>
        public Visitor getVisitor(string Name)
        {
            int visitorId = getId(Name);
            String sql = "SELECT v.Visitor_id,v.LastName,v.FirstName,e.MoneyDeposited FROM visitor v JOIN event_account e ON v.Visitor_Id=e.Visitor_id WHERE v.Visitor_Id= '" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<Visitor> temp;
            temp = new List<Visitor>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                string name1, name2;
                double balance;
                int no;

                while (reader.Read())
                {
                    no = Convert.ToInt32(reader["Visitor_id"]);
                    name1 = Convert.ToString(reader["LastName"]);
                    name2 = Convert.ToString(reader["FirstName"]);
                    balance = Convert.ToDouble(reader["MoneyDeposited"]);

                    temp.Add(new Visitor(no, name1, name2, balance));


                }

            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            return temp[0];
        }
        /// <summary>
        /// returns all the unreturned items for a certain visitor
        /// </summary>
        /// <param name="Name">visitors name</param>
        /// <returns></returns>
        public List<OverviewItem> GetRentedItem(string Name)
        {
            int visitorId = getId(Name);
            String sql = "Select p.ProductName, p.ProductId,p.UnitPrice, h.HasReturned From product_hired p, hire_receipt_line h, hire_receipt r WHERE  p.ProductId = h.ProductId And h.ReceiptNo = r.ReceiptNo AND r.Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<OverviewItem> temp;
            temp = new List<OverviewItem>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                string name;
                int id;
                int price;
                string returned;


                while (reader.Read())
                {
                    id = Convert.ToInt32(reader["ProductId"]);
                    name = Convert.ToString(reader["ProductName"]);
                    price = Convert.ToInt32(reader["UnitPrice"]);
                    returned = Convert.ToString(reader["HasReturned"]);

                    if (returned == "n")
                    {

                        temp.Add(new OverviewItem(id, name, price));
                    }
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            return temp;

        }
        /// <summary>
        /// returns a string saying if the visitor with the visitorId is still on the terrain.
        /// </summary>
        /// <param name="visitorId">visitor Id</param>
        /// <returns></returns>
        public string OnTerrain(int visitorId)
        {
            String sql = "SELECT HasEnteredEvent,HasLeftEvent FROM event_status WHERE Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);
            String Status;

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                string Enter;
                string Exit;


                while (reader.Read())
                {
                    Enter = Convert.ToString(reader["HasEnteredEvent"]);
                    Exit = Convert.ToString(reader["HasLeftEvent"]);


                    if (Exit == "y" || Enter == "n")
                    {
                        Status = "Left the Terrain";
                    }
                    else
                    {
                        Status = "On the Terrain";
                    }
                    return Status;
                }
                return " ";
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());

            }
            finally
            {
                connection.Close();
            }

            return "";
        }


        public int getId(string Name)// returns Id for a certain name.
        {
            int Id = 0;
            String sql = "SELECT Visitor_id FROM visitor WHERE CONCAT(FirstName,\" \",LastName) = Upper('" + Name + "')";
            MySqlCommand command = new MySqlCommand(sql, connection);


            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();


                while (reader.Read())
                {
                    Id = Convert.ToInt32(reader["Visitor_Id"]);
                }

            }
            catch
            {
                return -1;

            }
            finally
            {
                connection.Close();
            }

            return Id;
        }
        public List<int> getShopNo()
        {

            String sql = "SELECT DISTINCT (ShopNo)FROM food_receipt ORDER BY ShopNo ASC";
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<int> temp;
            temp = new List<int>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();


                int id;




                while (reader.Read())
                {
                    id = Convert.ToInt32(reader["ShopNo"]);
                    temp.Add(id);

                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            return temp;

        }

        public int getAmountSold(int ShopNo)
        {

            String sql = "SELECT COUNT(productId) FROM food_receipt r, receipt_line f WHERE r.ReceiptNo = f.ReceiptNo AND ShopNo =" + ShopNo;
            MySqlCommand command = new MySqlCommand(sql, connection);

            int number = 0;
            try
            {
                connection.Open();
                number = Convert.ToInt32(command.ExecuteScalar());
                return number;
            }
            catch
            {
                return -1;

            }
            finally
            {
                connection.Close();
            }

        }
        public List<String> getProductName(int ShopNo)
        {

            String sql = "SELECT DISTINCT (ProductName) FROM food_receipt r, receipt_line f, product p WHERE r.ReceiptNo = f.ReceiptNo AND p.ProductId = f.ProductId AND ShopNo =" + ShopNo;
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<String> temp;
            temp = new List<String>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();


                string proName;
                while (reader.Read())
                {
                    proName = Convert.ToString(reader["ProductName"]);
                    
                    temp.Add(proName);
                    
                    
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            
            return temp;
        }
        public int getSold(int ShopNo, string name)
        {
            int number = 0;


                String sql = "SELECT SUM(NoOfProducts) FROM food_receipt r, receipt_line f, product p WHERE r.ReceiptNo = f.ReceiptNo AND p.ProductId = f.ProductId AND ShopNo="+ShopNo+" AND ProductName ='"+name+"'";
                MySqlCommand command = new MySqlCommand(sql, connection);

                
                try
                {
                    connection.Open();
                   
                    number = Convert.ToInt32(command.ExecuteScalar());
                    return number;
                    

                   

                }
                catch
                {
                    return -1;

                }
                finally
                {
                    connection.Close();
                }
                return number;
            }
        public List<string> getProductInfo(List<string> productNames, int ShopNumber)
        {
            List<String> info;
            info = new List<string>();

            List<string> temp;
            temp = productNames;
            foreach (string v in temp)
            {
                info.Add(v +" " +getSold(ShopNumber, v));
            }
            return info;
        }
            
        }

    }
